package com.py.credit.service.impl;

import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import com.py.credit.common.ServiceException;
import com.py.credit.common.logs.ExceptionLogger;
import com.py.credit.dao.ColumnDao;
import com.py.credit.dao.TableDao;
import com.py.credit.entity.Column;
import com.py.credit.entity.DataResult;
import com.py.credit.entity.Dept;
import com.py.credit.entity.StatisticsNumResult;
import com.py.credit.entity.StatisticsResult;
import com.py.credit.entity.SysUser;
import com.py.credit.entity.Table;
import com.py.credit.entity.TableResult;
import com.py.credit.entity.common.InspectRule;
import com.py.credit.entity.common.OptionalColumn;
import com.py.credit.service.DeptService;
import com.py.credit.service.TableService;
import com.py.credit.utils.CharacterUtils;
import com.py.credit.utils.DateUtils;
import com.py.credit.utils.NumberUtils;
import com.py.credit.utils.SQLBuilder;
import com.py.framework.core.common.Page;
import com.py.framework.core.entity.SysOfficeEntity;

@Service
public class TableServcieImpl implements TableService {

    private static final Logger LOGGER = LoggerFactory.getLogger(TableServcieImpl.class);

    @Autowired
    TableDao tableDao;

    @Autowired
    DeptService deptService;

    @Autowired
    ColumnDao columnDao;

    @Override
    public List<Map<String, Object>> getByConditionForPage(String kId, String deptId, String type,
                                                           List<String> showColumns, String[] additionalColumns, String conditionStr, Page page) {
        if (CollectionUtils.isEmpty(showColumns)) {

            throw new ServiceException("至少选择一列");

        }
        List<String> conditions = SQLBuilder.createConditionSql(conditionStr);

        List<Map<String, Object>> tables = tableDao.selectByConditionForPage(kId, deptId, showColumns,
                additionalColumns, page, conditions, type);

        return tables;

    }

    @Override
    public Table queryCubeById(int tableId) {
        return tableDao.selectByPrimaryKey(tableId);
    }

    @Override
    public int getTableIdByResource(String resourceId) {
        return tableDao.getTableIdByResource(resourceId);
    }

    @Override
    @Transactional
    public void addTableInfo(String kId, String deptId, Table table, List<Column> columns) {

        Set<String> columnCode = new HashSet<String>();

        if (CollectionUtils.isEmpty(columns)) {

            throw new ServiceException("至少添加一列");
        }

        for (Column column : columns) {

            columnCode.add(column.getMetricCode());

            if (StringUtils.isBlank(column.getMetricName())) {

                throw new ServiceException("数据项名称不能为空");
            }
            if (StringUtils.isBlank(column.getMetricCode())) {

                throw new ServiceException("数据项编码不能为空");
            }

            if (StringUtils.isBlank(column.getFieldType())) {

                throw new ServiceException("数据项类型不能为空,数据项名称:" + column.getMetricName());
            }

            if ("number".equals(column.getFieldType()) && (column.getFieldLength() == null
                    || column.getFieldLength() > 63 || column.getFieldLength() <= 0)) {

                throw new ServiceException("数字类型长度范围1~63,数据项名称:" + column.getMetricName());
            }

            if ("varchar".equals(column.getFieldType()) && (column.getFieldLength() == null
                    || column.getFieldLength() > 4000 || column.getFieldLength() <= 0)) {

                throw new ServiceException("字符串类型长度范围1~4000,数据项名称:" + column.getMetricName());
            }

            column.setType("P");

        }

        if (columnCode.size() < columns.size()) {

            throw new ServiceException("字段编码不能重复");

        }

        String randomString = CharacterUtils.getRandomString(10);
        table.setResourceId(table.getResourceId() + "_" + randomString);
        String createTableSql = SQLBuilder.createTableSql(kId, "t_" + table.getResourceId(), columns);
        LOGGER.debug("TABLE CREATE  ==============>" + createTableSql);
        // 建表
        tableDao.operate(createTableSql);
        table.setShowType(StringUtils.isBlank(table.getShowType()) ? "M" : table.getShowType());
        table.setCollectionType(StringUtils.isBlank(table.getCollectionType()) ? "S" : table.getCollectionType());
        tableDao.insert(table);
        columnDao.addColumns(columns);
        // 常用的表(S)部门设为0
        if (!"P".equals(table.getType())) {
            deptId = "0";
        }
        tableDao.addDeptCube(kId, deptId, table.getTableId(), table.getOrder());
        tableDao.addColunmCube(columns, table.getTableId());
        tableDao.addColunmCubeInstance(columns, table.getTableId());

    }

    @Override
    @Transactional
    public void updateTable(String kId, String deptId, Table table, List<Column> addColumns, List<Column> updateColumns,
                            List<Column> delColumns) {

        if (!CollectionUtils.isEmpty(addColumns)) {

            for (Column column : addColumns) {

                if (StringUtils.isBlank(column.getMetricName())) {

                    throw new ServiceException("数据项名称不能为空");
                }
                if (StringUtils.isBlank(column.getMetricCode())) {

                    throw new ServiceException("数据项编码不能为空");
                }

                if (StringUtils.isBlank(column.getFieldType())) {

                    throw new ServiceException("数据项类型不能为空,数据项名称:" + column.getMetricName());
                }

                if ("number".equals(column.getFieldType()) && (column.getFieldLength() == null
                        || column.getFieldLength() > 63 || column.getFieldLength() <= 0)) {

                    throw new ServiceException("数字类型长度范围1~63");
                }

                if ("varchar".equals(column.getFieldType()) && (column.getFieldLength() == null
                        || column.getFieldLength() > 12000 || column.getFieldLength() <= 0)) {

					throw new ServiceException("字符串类型长度范围1~12000");
				}

				if (this.duplicateExist(column.getMetricCode(), table.getTableId())) {

                    throw new ServiceException("你输入的数据项:" + column.getMetricCode() + "已经存在或者已经删除，请重新输入");
                }

                column.setType("P");

            }

            for (Column col : addColumns) {

                if (StringUtils.isBlank(col.getMetricName())) {

                    throw new ServiceException("数据项名称不能为空");
                }
                if (StringUtils.isBlank(col.getMetricCode())) {

                    throw new ServiceException("数据项编码不能为空");
                }

                if (StringUtils.isBlank(col.getFieldType())) {

                    throw new ServiceException("数据项类型不能为空,数据项名称:" + col.getMetricName());
                }

                if ("number".equals(col.getFieldType())
                        && (col.getFieldLength() == null || col.getFieldLength() > 63 || col.getFieldLength() <= 0)) {

                    throw new ServiceException("数字类型长度范围1~63");
                }

                if ("varchar".equals(col.getFieldType())
                        && (col.getFieldLength() == null || col.getFieldLength() > 12000 || col.getFieldLength() <= 0)) {

                    throw new ServiceException("字符串类型长度范围1~12000");
                }

                col.setType("P");

                String addColumnSql = SQLBuilder.addColumnSql("t_" + table.getResourceId(), col);
                LOGGER.debug("TABLE ALTER  ==============>" + addColumnSql);
                tableDao.operate(addColumnSql);
            }

            columnDao.addColumns(addColumns);
            tableDao.addColunmCube(addColumns, table.getTableId());
            tableDao.addColunmCubeInstance(addColumns, table.getTableId());

        }

        if (!CollectionUtils.isEmpty(updateColumns)) {
            // 给表跟新列,更新t_dc_metric和实体表

            for (Column column : updateColumns) {
            	if (StringUtils.isBlank(column.getMetricName())) {

                    throw new ServiceException("数据项名称不能为空");
                }
                if (StringUtils.isBlank(column.getMetricCode())) {

                    throw new ServiceException("数据项编码不能为空");
                }

                if (StringUtils.isBlank(column.getFieldType())) {

                    throw new ServiceException("数据项类型不能为空,数据项名称:" + column.getMetricName());
                }

                if ("number".equals(column.getFieldType())
                        && (column.getFieldLength() == null || column.getFieldLength() > 63 || column.getFieldLength() <= 0)) {

                    throw new ServiceException("数字类型长度范围1~63");
                }

                if ("varchar".equals(column.getFieldType())
                        && (column.getFieldLength() == null || column.getFieldLength() > 12000 || column.getFieldLength() <= 0)) {

                    throw new ServiceException("字符串类型长度范围1~12000");
                }
                column.setType("P");

                columnDao.updateByPrimaryKey(column);

                columnDao.updateColunmCubeSort(table.getTableId(), column.getOrder(), column.getMetricId());

                 tableDao.operate(SQLBuilder.modifyColumnSql("t_"+table.getResourceId(),
                 column));
            }

        }

        if (!CollectionUtils.isEmpty(delColumns)) {
            columnDao.delColumns(delColumns);
            tableDao.delCubeMetric(delColumns, table.getTableId());
        }

        // 更新t_dc_cubes

        tableDao.updateByPrimaryKeySelective(table);

        tableDao.updateDeptCubeSort(kId, deptId, table.getTableId(), table.getOrder());

    }

    /**
     * @param metricCode
     * @param tableId
     * @return
     */
    @Override
    public boolean duplicateExist(String metricCode, Integer tableId) {

        return tableDao.selectTableColumn(metricCode, tableId) > 0;
    }

    @Override
    public Map<String, Object> getTableInfo(String kId, String deptId, int tableId) {

        Map<String, Object> map = new HashMap<String, Object>();
        Map<String, Object> basic = new HashMap<String, Object>();
        Map<String, Object> metaBasic = new HashMap<String, Object>();
        Map<String, Object> provider = new HashMap<String, Object>();
        Map<String, Object> maintainer = new HashMap<String, Object>();
        Table table = tableDao.select(kId, tableId, deptId);

        SysOfficeEntity se = deptService.getDeptById(deptId);

        if (se != null) {
            basic.put("deptName", se.getOfficeName());
        }else {
            basic.put("deptName", "");
        }

        List<Column> columns = columnDao.selectListByTableId(tableId);
        basic.put("content", table.getContent());
        basic.put("showType", table.getShowType());

        basic.put("order", table.getOrder());
        basic.put("resourceId", table.getResourceId());
        basic.put("resourceTitle", table.getResourceTitle());
        basic.put("summary", table.getSummary());
        basic.put("kId", kId);
        basic.put("deptId", deptId);
        basic.put("informationType",table.getInformationType());
        metaBasic.put("content", table.getContent());
        metaBasic.put("managementStyle", table.getManagementStyle());
        metaBasic.put("sharingType", table.getSharingType());
        metaBasic.put("resourceCategory", table.getResourceCategory());
        metaBasic.put("updateFrequency", table.getUpdateFrequency());
        metaBasic.put("dateOfPublication",
                DateUtils.dateToString(table.getDateOfPublication(), DateUtils.STANDADD_PATTERN));
        metaBasic.put("collectionType", table.getCollectionType());
        provider.put("serviceUrl", table.getServiceUrl());
        provider.put("serviceType", table.getServiceType());
        provider.put("online", table.getOnline());
        provider.put("organisationName", table.getOrganisationName());
        provider.put("providerAddress", table.getProviderAddress());
        provider.put("providerContact", table.getProviderContact());

        maintainer.put("maintainerAddress", table.getMaintainerAddress());
        maintainer.put("metadataContact", table.getMetadataContact());
        map.put("column", columns);
        map.put("basic", basic);
        map.put("metaBasic", metaBasic);
        map.put("provider", provider);
        map.put("maintainer", maintainer);

        return map;
    }

    @Override
    public void addTable(Table table) {
        tableDao.insert(table);

    }

    @Override
    public void updateTable(Table table) {
        tableDao.updateByPrimaryKey(table);

    }

    @Override
    public List<Map<String, Object>> getSharingResouceByConditionForPage(String kId, String deptId,
                                                                         List<String> showColumns, String[] additionalColumns, String condition, Page page) {

        if (CollectionUtils.isEmpty(showColumns)) {

            throw new ServiceException("至少选择一列");

        }

        List<String> conditions = SQLBuilder.createConditionSql(condition);

        List<Map<String, Object>> tables = tableDao.selectSharingResouceByConditionForPage(kId, deptId, showColumns,
                additionalColumns, page, conditions);

        return tables;
    }

    @Override
    @Transactional
    public synchronized int getAndSetMaxSerialNumberByDeptId(long deptId) {

        int maxSerialNumber = tableDao.selectMaxSerialNumberByDeptId(deptId);
        tableDao.addSerialNumber(maxSerialNumber + 1, deptId);
        return maxSerialNumber + 1;
    }

    @Override
    public List<DataResult> getData(String tableName, List<Column> columns, String tybm) {
    	if(tableName.equals("t_1000000010001_3ioj4GeZQU")) {
    		return tableDao.selectXzxkData(tableName, columns, tybm);
    	}else if(tableName.equals("t_1000000010002_XSni4zSzP0")) {
    		return tableDao.selectXzcfData(tableName, columns, tybm);
    	}else {
    		return tableDao.selectData(tableName, columns, tybm);
    	}
        
    }

    @Override
    public List<Map<String, Object>> getRecordsByConditionForPage(List<String>recordIds,String tableName,String deptId, List<String> showColumns,
                                                                  String[] additionalColumns, String condition, Page page) {
        if (CollectionUtils.isEmpty(showColumns)) {

            throw new ServiceException("至少选择一列");

        }
        List<String> conditions = SQLBuilder.createConditionSql(condition);
        List<Map<String, Object>> selectRecordsByConditionForPage = tableDao.selectRecordsByConditionForPage(recordIds,tableName,deptId,
                showColumns, additionalColumns, conditions, page);
        // 身份证脱敏
        if (showColumns.contains("B000002")) {
            if (!CollectionUtils.isEmpty(selectRecordsByConditionForPage)) {
                for (Map<String, Object> map : selectRecordsByConditionForPage) {
                    String idEncrypt = NumberUtils
                            .idEncrypt(map.get("B000002") != null ? map.get("B000002").toString() : "");
                    map.put("B000002", idEncrypt);
                }
            }
        }
        return selectRecordsByConditionForPage;

    }

    @Override
    public List<Map<String, Object>> getRecordsForXypjForPage(String tybm, String tableName, List<String> showColumns,
                                                              String[] additionalColumns, String condition, Page page) {
        if (CollectionUtils.isEmpty(showColumns)) {

            throw new ServiceException("至少选择一列");

        }
        List<String> conditions = SQLBuilder.createConditionSql(condition);
        List<Map<String, Object>> selectRecordsByConditionForPage = tableDao.selectRecordsForXypjForPage(tybm,
                tableName, showColumns, additionalColumns, conditions, page);

        return selectRecordsByConditionForPage;

    }

    @Override
    public List<Map<String, Object>> getTableList(String rootId) {

        List<Map<String, Object>> tableList = tableDao.selectCommonResource(rootId);

        return tableList;

    }

    @Override
    @Transactional
    public void del(String ids, String type, String kId, String deptId) {
        String[] tableIds = ids.split(",");

        // delete
        tableDao.delDeptCube(tableIds, kId, deptId);

        if (type == null || !type.equals("S")) {

            // 逻辑删 update
            tableDao.delColumn(tableIds);
            // delete
            tableDao.delTableColumn(tableIds);
            // tableDao.delTableColumnInstance(tableIds);
            // 逻辑删 update
            tableDao.del(tableIds);
        }

    }

    @Override
    public Table getTableById(int tableId) {
        return tableDao.selectByPrimaryKey(tableId);
    }

    @Override
    public Map<String, Object> getSharingTableInfo(String kId, String deptId, int tableId) {
        Map<String, Object> map = new HashMap<String, Object>();
        Map<String, Object> basic = new HashMap<String, Object>();
        Map<String, Object> metaBasic = new HashMap<String, Object>();
        Map<String, Object> provider = new HashMap<String, Object>();
        Map<String, Object> maintainer = new HashMap<String, Object>();
        Table table = tableDao.selectSharingTable(kId, tableId);

        List<Column> columns = columnDao.selectListByTableId(tableId);
        basic.put("content", table.getContent());
        basic.put("sjlb", table.getSjlb());
        basic.put("order", table.getOrder());
        basic.put("resourceId", table.getResourceId());
        basic.put("resourceTitle", table.getResourceTitle());
        basic.put("summary", table.getSummary());
        basic.put("informationType", table.getInformationType());
        metaBasic.put("content", table.getContent());
        metaBasic.put("managementStyle", table.getManagementStyle());
        metaBasic.put("sharingType", table.getSharingType());

        metaBasic.put("resourceCategory", table.getResourceCategory());
        metaBasic.put("updateFrequency", table.getUpdateFrequency());
        metaBasic.put("dateOfPublication",
                DateUtils.dateToString(table.getDateOfPublication(), DateUtils.STANDADD_PATTERN));
        metaBasic.put("collectionType", table.getCollectionType());
        provider.put("serviceUrl", table.getServiceUrl());
        provider.put("serviceType", table.getServiceType());
        provider.put("online", table.getOnline());
        provider.put("organisationName", table.getOrganisationName());
        provider.put("providerAddress", table.getProviderAddress());
        provider.put("providerContact", table.getProviderContact());

        maintainer.put("maintainerAddress", table.getMaintainerAddress());
        maintainer.put("metadataContact", table.getMetadataContact());
        map.put("column", columns);
        map.put("basic", basic);
        map.put("metaBasic", metaBasic);
        map.put("provider", provider);
        map.put("maintainer", maintainer);

        return map;
    }

    @Override
    public List<Dept> getAlreadyDistributeRecord(int tableId, String kId) {

        return tableDao.selectAlreadyDistributeRecord(tableId, kId);
    }

    @Override
    @Transactional
    public void distributeCommonResource(String kId, int tableId, List<Map<String, String>> depts) {

        tableDao.delDistributeCube(tableId, kId);

        for (Map<String, String> map : depts) {

            tableDao.addDeptCube(kId, map.get("deptId"), tableId,
                    map.get("sort") == null ? 0 : Integer.valueOf(map.get("sort")));

        }

    }

    @Override
    public List<Map<String, Object>> getTableList(String kId, String deptId, String sharingContentType) {
        // 一站式查询目录中授权查询的表
        List<Map<String, Object>> resources = tableDao.selectSharingResourceBy(kId, sharingContentType, deptId);

        return resources;
    }

    /**
     * @param repeatCheck 是否验重 Y:是 N:否
     * @param userInfo    用户信息实体
     * @param id          表ID
     * @Function 更新重复校验字段状态
     * @FileName TableServcieImpl.java
     * @MethodName updateRepeatCheck
     * @Date 2018年9月10日 下午3:48:31
     * @Author Min DongXv
     */
    @Override
    public void updateRepeatCheck(String repeatCheck, SysUser userInfo, Integer id) {
        tableDao.updateRepeatCheck(repeatCheck, userInfo.getUsername(), id);
    }

    @Override
    public int getByResourceId(String resourceId) {
        return tableDao.selectByResourceId(resourceId);
    }

    @Override
    public List<LinkedHashMap> selectTableInfo(List<Integer> ids, Integer rootId) {

        return tableDao.selectTableInfo(ids, rootId);
    }

    @Override
    public List<Table> getResources(String kId, String deptId, String name) {

        return tableDao.selectResources(kId, deptId, name);
    }

	@Override
	public List<InspectRule> getResourceColumnRules(Integer version, String tableId, String ruleName, Page page) {

		return tableDao.selectResourceColumnRulesForPage(version, tableId, ruleName, page);
	}

	@Override
	public List<InspectRule> getResourceTableRules(Integer version, String tableId, String ruleName, Page page) {

		return tableDao.selectResourceTableRulesForPage(version, tableId, ruleName, page);
	}

    @Override
    public List<Map<String, Object>> getRecordsForPage(String tybm, String tyshxydm, String tableId, String tableName, List<String> showColumns,
                                                       String[] additionalColumns, String condition, Page page) {
        if (CollectionUtils.isEmpty(showColumns)) {
            throw new ServiceException("至少选择一列");
        }
        List<String> conditions = SQLBuilder.createConditionSql(condition);
        List<Map<String, Object>> selectRecordsByConditionForPage = tableDao.selectRecordsForPage(
                tybm, tyshxydm, tableName, tableId, showColumns, additionalColumns, conditions, page);
        return selectRecordsByConditionForPage;

    }

	@Override
	public List<StatisticsResult> getStatistics(String startTime, String endTime, List<String> deptIds, List<String> tableIds) {
		
		List<StatisticsResult> result = new ArrayList<>();
		String infoId = tableDao.getleasterInfoId();
		List<Map<String,String>> tableList = tableDao.getTables(null,infoId,tableIds);
		List<Map<String,Object>> deptList = tableDao.getDeptList(deptIds);
		for (Map<String, Object> map : deptList) {
			StatisticsResult oneStatisticsResult = new StatisticsResult();
			oneStatisticsResult.setDeptName(String.valueOf(map.get("deptName")));
			List<StatisticsNumResult> numResult = tableDao.getStatistics(startTime,endTime,Long.parseLong(String.valueOf(map.get("ID"))),tableList,infoId);
			int totalNum = 0;
			for (StatisticsNumResult statisticsNumResult : numResult) {
				int num = Integer.parseInt(statisticsNumResult.getNum());
				totalNum += num;
			}
			StatisticsNumResult totalNumResult = new StatisticsNumResult();
			totalNumResult.setCubeName("总计");
			totalNumResult.setNum(String.valueOf(totalNum));
			oneStatisticsResult.setDataNums(numResult);
			
			result.add(oneStatisticsResult);
		}
		StatisticsResult oneStatisticsResult = new StatisticsResult();
		oneStatisticsResult.setDeptName("总计");
		oneStatisticsResult.setDataNums(tableDao.getTotalStatistics(startTime,endTime,deptList,tableList));
		result.add(oneStatisticsResult);
		return result;
	}

	@Override
	public List<Map<String, String>> getTableListForSearch(List<String> deptId) {
		String infoId = tableDao.getleasterInfoId();
		
		return tableDao.getTables(deptId,infoId,null);
	}

	@Override
	public void exportStatistics(String startTime, String endTime, List<StatisticsResult> data, HttpServletResponse response) {
		ExceptionLogger.error("SsearchController#exportStatistics", "统计下载异常！ERROR:" + data.toString());
		//创建HSSFWorkbook对象(excel的文档对象)
		HSSFWorkbook wb = new HSSFWorkbook();
		//建立新的sheet对象（excel的表单）
		HSSFSheet sheet=wb.createSheet("数据归集统计");
		//设置单元格风格，居中对齐.
        HSSFCellStyle cs = wb.createCellStyle();
        cs.setAlignment(HSSFCellStyle.ALIGN_CENTER); 

		//在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
		HSSFRow row1=sheet.createRow(0);
		//创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
		HSSFCell cell=row1.createCell(0);
		      //设置单元格内容
		cell.setCellValue("数据归集统计");
		 cell.setCellStyle(cs);
		HSSFRow rowTime=sheet.createRow(1);
		HSSFCell cellTime=rowTime.createCell(0);
		cellTime.setCellValue(startTime+"至"+endTime);
		cellTime.setCellStyle(cs);
		//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
		sheet.addMergedRegion(new CellRangeAddress(0,0,0,data.get(0).getDataNums().size()));
		sheet.addMergedRegion(new CellRangeAddress(1,1,0,data.get(0).getDataNums().size()));
		//在sheet里创建第二行
		for(int j=0;j<data.size();j++) {
			ExceptionLogger.error("SsearchController#exportStatistics", "统计下载异常！ERROR:" + data.get(0).getDataNums().get(0).getCubeName()+"--"+data.get(0).getDataNums().get(0).getNum());
			if(j==0) {
				HSSFRow row2=sheet.createRow(j+2);    
				for(int i = 0;i < data.get(j).getDataNums().size();i++) {
			    	 row2.createCell(i+1).setCellValue(data.get(j).getDataNums().get(i).getCubeName());
			     }
			}
			HSSFRow row2=sheet.createRow(j+3);
			row2.createCell(0).setCellValue(data.get(j).getDeptName());	
			for(int i = 0;i < data.get(j).getDataNums().size();i++) {
				row2.createCell(i+1).setCellValue(data.get(j).getDataNums().get(i).getNum());
			}
		}
		 
		//输出Excel文件
		    OutputStream output;
			try {
				output = response.getOutputStream();
				response.reset();
		        String  codedFileName = URLEncoder.encode(startTime+"至"+endTime+"信息资源统计.xls", "UTF-8");
				response.setHeader("Content-disposition", "attachment; filename="+ codedFileName);
				response.setContentType("application/msexcel");        
				wb.write(output);
				output.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}

	@Override
	public void getTableDatas(String startTime, String endTime, String deptId, String tableId,HttpServletResponse response) {
		String infoId = tableDao.getleasterInfoId();
		TableResult tableResult = tableDao.getTableColumns(tableId,infoId);
		List<Column> columns = tableResult.getColumns();
		List<String> showColumns = new ArrayList<>();
		for (Column column : columns) {
			showColumns.add(column.getMetricCode());
		}
		List<Map<String,Object>> data = tableDao.getTableDatas(StringUtils.join(showColumns, ","),"t_"+tableResult.getTableNameEN(),deptId,startTime,endTime);
				 // 生成提示信息
		        response.setContentType("application/vnd.ms-excel");
				//创建HSSFWorkbook对象(excel的文档对象)
				HSSFWorkbook wb = new HSSFWorkbook();
				//建立新的sheet对象（excel的表单）
				HSSFSheet sheet=wb.createSheet(tableResult.getTableNameCN());
				//在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
				HSSFRow row1=sheet.createRow(0);
				//创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
				HSSFCell cell=row1.createCell(0);
				      //设置单元格内容
				cell.setCellValue(tableResult.getTableNameCN());
				HSSFRow rowTime=sheet.createRow(1);
				HSSFCell cellTime=rowTime.createCell(0);
				cellTime.setCellValue(startTime+"至"+endTime);
				//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
				sheet.addMergedRegion(new CellRangeAddress(0,0,0,showColumns.size()-1));
				sheet.addMergedRegion(new CellRangeAddress(1,1,0,showColumns.size()-1));
				HSSFRow row2=sheet.createRow(2);    
				for(int i = 0;i < showColumns.size();i++) {
			    	 row2.createCell(i).setCellValue(columns.get(i).getMetricName());
			     }
				//在sheet里创建第二行
				for(int j=0;j<data.size();j++) {
					HSSFRow row3=sheet.createRow(j+3); 
					 for(int i = 0;i < showColumns.size();i++) {
						 String value = String.valueOf(data.get(j).get(showColumns.get(i)));
						 if(value != null &&  !"null".equals(value)) {
							 row3.createCell(i).setCellValue(value);
						 }else {
							 row3.createCell(i).setCellValue("");
						 }
				     }
					
				}
				 
				//输出Excel文件
				    OutputStream output;
					try {
						output = response.getOutputStream();
						response.reset();
				        String  codedFileName = URLEncoder.encode(tableResult.getTableNameCN(), "UTF-8");
						response.setHeader("Content-disposition", "attachment; filename="+codedFileName+".xls");
						response.setContentType("application/msexcel");        
						wb.write(output);
						output.close();
					} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
	}

	
	/* (非 Javadoc)
	 *
	 
	 *
	 
	 * @param condition
	 * @param type
	 * @param deptId
	 * @param response
	 * @see com.py.credit.service.TableService#exportSgsTotal(java.lang.String, java.lang.String, java.lang.String, javax.servlet.http.HttpServletResponse)
	 */
	
	@Override
	public List<Map<String,Object>> getSgsTotalByConditionForPage(List<String> showColumns,
			String condition, String type, List<String> deptId ,
			HttpServletResponse response, String startTime, String endTime,Page page,String jdrqStart,String jdrqEnd) {
		
		List<String> conditions = SQLBuilder.createConditionSql(condition);
		StringBuilder advanceSearchSql = new StringBuilder();
		if (!CollectionUtils.isEmpty(conditions)) {

			advanceSearchSql.append(" and");

			for (String cd : conditions) {

				advanceSearchSql.append(cd).append(" and ");
			}

			advanceSearchSql.setLength(advanceSearchSql.length() - " and ".length());

		}
		List<Map<String,Object>> data = new ArrayList<>();
		if(type.equals("许可")) {
			data=tableDao.getSgsXkTotalForPage( deptId, advanceSearchSql,startTime,endTime,page,jdrqStart,jdrqEnd);
		}else {
			data=tableDao.getSgsCfTotalForPage( deptId, advanceSearchSql,startTime,endTime,page,jdrqStart,jdrqEnd);
		}
	
		
	return data;
		
	}

	

	
	@Override
	public List<Map<String, Object>> getSgsDept(String type) {
		String infoId = tableDao.getleasterInfoId();
		List<Map<String,Object>> data = new ArrayList<>();
		if(type.equals("许可")) {
			data=tableDao.getSgsXkDept(infoId);
		}else {
			data=tableDao.getSgsCfDept(infoId);
		}
		return data;
	}

	
	

	
	@Override
	public List<Map<String, Object>> getListByTableIdForPage(String xmmc,String tableId,  List<String> deptId,Page page) {
		String infoId = tableDao.getleasterInfoId();
		//更加tableId获取表参数
		TableResult tableResult = tableDao.getTableColumns(tableId,infoId);
		return tableDao.getListByTableIdForPage(xmmc,"t_"+tableResult.getTableNameEN(),deptId,page);
	}

	
	/* (非 Javadoc)
	 *
	 
	 *
	 
	 * @param tableId
	 * @return
	 * @see com.py.credit.service.TableService#getDeptByTableId(java.lang.String)
	 */
	
	@Override
	public List<Map<String, Object>> getDeptByTableId(String tableId) {
		String infoId = tableDao.getleasterInfoId();
	
	return tableDao.getDeptByTableId(tableId,infoId);
	}
	
	public List<Map<String, Object>> getSgsDataListByConditionForPage(List<String>  recordIds,List<String> showColumns, String[] additionalColumns,
			String condition, Page page, String type, List<String> deptId,String startTime,String endTime,String jdrqStart,String jdrqEnd){
		List<String> conditions = SQLBuilder.createConditionSql(condition);
		StringBuilder advanceSearchSql = new StringBuilder();
		if (!CollectionUtils.isEmpty(conditions)) {

			advanceSearchSql.append(" and");

			for (String cd : conditions) {

				advanceSearchSql.append(cd).append(" and ");
			}

			advanceSearchSql.setLength(advanceSearchSql.length() - " and ".length());

		}
		List<Map<String,Object>> data = new ArrayList<>();
		if(type.equals("许可")) {
		
			data=tableDao.getXkDataListForPage(recordIds ,deptId, advanceSearchSql,startTime,endTime,page,jdrqStart,jdrqEnd);
		}else {
			data=tableDao.getCfDataListlForPage(recordIds, deptId, advanceSearchSql,startTime,endTime,page,jdrqStart,jdrqEnd);
		}
			
				
	return data;
		
	}

	public List<Map<String, Object>> getSgsDataListByCondition(List<String>  recordIds,List<String> showColumns, String[] additionalColumns,
			String condition,  String type, List<String> deptId,String startTime,String endTime,String jdrqStart,String jdrqEnd){
		List<String> conditions = SQLBuilder.createConditionSql(condition);
		StringBuilder advanceSearchSql = new StringBuilder();
		if (!CollectionUtils.isEmpty(conditions)) {

			advanceSearchSql.append(" and");

			for (String cd : conditions) {

				advanceSearchSql.append(cd).append(" and ");
			}

			advanceSearchSql.setLength(advanceSearchSql.length() - " and ".length());

		}
		List<Map<String,Object>> data = new ArrayList<>();
		if(type.equals("许可")) {
		
			data=tableDao.getXkDataList(recordIds ,deptId, advanceSearchSql,startTime,endTime,jdrqStart,jdrqEnd);
		}else {
			data=tableDao.getCfDataList(recordIds, deptId, advanceSearchSql,startTime,endTime,jdrqStart,jdrqEnd);
		}
			
				
	return data;
		
	}
	
	/* (非 Javadoc)
	 *
	 
	 *
	 
	 * @param xmmc
	 * @param xzxdrlb
	 * @param sxyj
	 * @param tableId
	 * @param deptId
	 * @param page
	 * @return
	 * @see com.py.credit.service.TableService#getDataByTableIdForPage(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.util.List, com.py.framework.core.common.Page)
	 */
	
	@Override
	public List<Map<String, Object>> getDataByTableIdForPage(String xmmc, String xzxdrlb, String sdyj, String tableId,
			List<String> deptId, Page page) {

		String infoId = tableDao.getleasterInfoId();
		//更加tableId获取表参数
		TableResult tableResult = tableDao.getTableColumns(tableId,infoId);
		return tableDao.getDataByTableIdForPage(xmmc,"t_"+tableResult.getTableNameEN(),xzxdrlb,sdyj,deptId,page);
	
	}

	
	/* (非 Javadoc)
	 *
	 
	 *
	 
	 * @param tableId
	 * @param condition
	 * @param page
	 * @return
	 * @see com.py.credit.service.TableService#getOtherTotalByTableIdForPage(java.lang.String, java.lang.String, com.py.framework.core.common.Page)
	 */
	
	@Override
	public List<Map<String, Object>> getOtherTotalByTableIdForPage(String tableId, String condition, Page page) {
		List<String> conditions = SQLBuilder.createConditionSql(condition);
		StringBuilder advanceSearchSql = new StringBuilder();
		if (!CollectionUtils.isEmpty(conditions)) {

			advanceSearchSql.append(" and");

			for (String cd : conditions) {

				advanceSearchSql.append(cd).append(" and ");
			}

			advanceSearchSql.setLength(advanceSearchSql.length() - " and ".length());

		}
		
		String infoId = tableDao.getleasterInfoId();
		//更加tableId获取表参数
		TableResult tableResult = tableDao.getTableColumns(tableId,infoId);
		return tableDao.getOtherTotalByTableIdForPage("t_"+tableResult.getTableNameEN(),advanceSearchSql,page);
	
	}

	
	/* (非 Javadoc)
	 *
	 
	 *
	 
	 * @param page
	 * @param type
	 * @param deptId
	 * @param startTime
	 * @param endTime
	 * @param jdrqStart
	 * @param jdrqEnd
	 * @param wsh
	 * @param xzxdrmc
	 * @return
	 * @see com.py.credit.service.TableService#getSgstzListForPage(com.py.framework.core.common.Page, java.lang.String, java.util.List, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
	 */
	
	@Override
	public List<Map<String, Object>> getSgstzListForPage(Page page, String type, List<String> deptId, String startTime,
			String endTime, String jdrqStart, String jdrqEnd, String wsh, String xzxdrmc,List<String> recordIds) {
		List<Map<String,Object>> data = new ArrayList<>();
			if(type.equals("xzxk")) {
				data =	tableDao.getXktzListForPage(page,deptId,startTime,endTime,jdrqStart,jdrqEnd,wsh,xzxdrmc);
			}else {
				data =	tableDao.getCftzListForPage(page,deptId,startTime,endTime,jdrqStart,jdrqEnd,wsh,xzxdrmc);
			}
		return data;
	}
	
	
	@Override
	public List<Map<String, Object>> getSgstzList(Page page, String type, List<String> deptId, String startTime,
			String endTime, String jdrqStart, String jdrqEnd, String wsh, String xzxdrmc,List<String> recordIds) {
		List<Map<String,Object>> data = new ArrayList<>();
			if(type.equals("xzxk")) {
				data =	tableDao.getXktzList(page,deptId,startTime,endTime,jdrqStart,jdrqEnd,wsh,xzxdrmc,recordIds);
			}else {
				data =	tableDao.getCftzList(page,deptId,startTime,endTime,jdrqStart,jdrqEnd,wsh,xzxdrmc,recordIds);
			}
		return data;
	}

	
	/* (非 Javadoc)
	 *
	 
	 *
	 
	 * @param page
	 * @param type
	 * @param deptId
	 * @param startTime
	 * @param endTime
	 * @return
	 * @see com.py.credit.service.TableService#getSgsCblListForPage(com.py.framework.core.common.Page, java.lang.String, java.util.List, java.lang.String, java.lang.String)
	 */
	
	@Override
	public List<Map<String, Object>> getSgsCblListForPage(Page page, String type, List<String> deptId, String startTime,
			String endTime) {
		List<Map<String,Object>> data = new ArrayList<>();
		if(type.equals("xzxk")) {
			data =	tableDao.getXkCblListForPage(page,deptId,startTime,endTime);
		}else {
			data =	tableDao.getCfCblListForPage(page,deptId,startTime,endTime);
		}
	return data;
	}

	@Override
	public List<Map<String, Object>> getLyTableListByConditionForPage(String kId, String deptId, String type,
			List<String> showColumns, String[] additionalColumns, String condition, Page page) {
		 if (CollectionUtils.isEmpty(showColumns)) {

	            throw new ServiceException("至少选择一列");

	        }
	        List<String> conditions = SQLBuilder.createConditionSql(condition);

	        List<Map<String, Object>> tables = tableDao.selectLyByConditionForPage(kId, deptId, showColumns,
	                additionalColumns, page, conditions, type);

	        return tables;

	}



	}


